package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.db.BeanBase;
import com.db.Table;

import db.beans.M_CurrencyRateBean;

public class M_CurrencyRateTable extends Table {

	public static final String TABLE_NAME = "M_CURRENCY_RATE";
	public static final String COL_CURRENCY_ID = "CURRENCY_ID";
	public static final String COL_RATE = "RATE";
	public static final String COL_REFIX_DATE = "REFIX_DATE";
	public static final String F_COL_CURRENCY_ID = TABLE_NAME + "." + "CURRENCY_ID";
	public static final String F_COL_RATE = TABLE_NAME + "." + "RATE";
	public static final String F_COL_REFIX_DATE = TABLE_NAME + "." + "REFIX_DATE";

	public static BeanBase[] selectAll(Connection conn) throws SQLException {
		final String sql = "SELECT "
				+ COL_CURRENCY_ID + ","
				+ COL_RATE + ","
				+ COL_REFIX_DATE + ","
				+ " FROM " + TABLE_NAME;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<M_CurrencyRateBean> v = new Vector<M_CurrencyRateBean>();
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i;
			while (rs.next()) {
				i = 0;
				M_CurrencyRateBean rec = new M_CurrencyRateBean();
				rec.setCurrencyId(rs.getInt(++i));
				rec.setRate(rs.getFloat(++i));
				rec.setRefixDate(rs.getDate(++i));
				v.add(rec);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null) {
				stmt.close();
			}
		}
		return (M_CurrencyRateBean[]) v.toArray(new M_CurrencyRateBean[v.size()]);
	}

	public static Double selectLatestRate(Connection conn, int currencyCode) throws SQLException {
		final String sql = "(SELECT " + "MAX(" + COL_REFIX_DATE +")" + " FROM " + TABLE_NAME + " WHERE " + COL_CURRENCY_ID + "=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Double rate = 0.0;
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, currencyCode);
			rs = stmt.executeQuery();
			while (rs.next()) {
				rate = rs.getDouble(1);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null) {
				stmt.close();
			}
		}
		return rate;
	}
}
